package com.bookmanager.dao;

import com.bookmanager.model.Category;
import com.bookmanager.util.DatabaseUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CategoryDAO {

    // 获取所有分类
    public List<Category> getAllCategories() {
        List<Category> categories = new ArrayList<>();
        String sql = "SELECT c.*, " +
                "(SELECT COUNT(*) FROM books b WHERE b.category = c.category_name) as book_count " +
                "FROM book_categories c " +
                "ORDER BY c.create_time DESC";

        try (Connection conn = DatabaseUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                categories.add(extractCategoryFromResultSet(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return categories;
    }

    // 根据ID获取分类
    public Category getCategoryById(int categoryId) {
        String sql = "SELECT c.*, " +
                "(SELECT COUNT(*) FROM books b WHERE b.category = c.category_name) as book_count " +
                "FROM book_categories c " +
                "WHERE c.category_id = ?";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, categoryId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return extractCategoryFromResultSet(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 添加分类
    public boolean addCategory(Category category) {
        String sql = "INSERT INTO book_categories (category_name, description) VALUES (?, ?)";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, category.getCategoryName());
            stmt.setString(2, category.getDescription());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 更新分类
    public boolean updateCategory(Category category) {
        String sql = "UPDATE book_categories SET category_name = ?, description = ? WHERE category_id = ?";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, category.getCategoryName());
            stmt.setString(2, category.getDescription());
            stmt.setInt(3, category.getCategoryId());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 删除分类
    public boolean deleteCategory(int categoryId) {
        // 先检查是否有图书使用这个分类
        if (hasBooksInCategory(categoryId)) {
            return false; // 有图书使用，不能删除
        }

        String sql = "DELETE FROM book_categories WHERE category_id = ?";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, categoryId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 检查分类下是否有图书（通过分类名称检查）
    private boolean hasBooksInCategory(int categoryId) {
        // 先获取分类名称
        String categoryName = getCategoryNameById(categoryId);
        if (categoryName == null) {
            return false;
        }

        String sql = "SELECT COUNT(*) FROM books WHERE category = ?";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, categoryName);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 根据分类ID获取分类名称
    private String getCategoryNameById(int categoryId) {
        String sql = "SELECT category_name FROM book_categories WHERE category_id = ?";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, categoryId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getString("category_name");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private Category extractCategoryFromResultSet(ResultSet rs) throws SQLException {
        Category category = new Category();
        category.setCategoryId(rs.getInt("category_id"));
        category.setCategoryName(rs.getString("category_name"));
        category.setDescription(rs.getString("description"));
        category.setBookCount(rs.getInt("book_count"));

        Timestamp createTime = rs.getTimestamp("create_time");
        if (createTime != null) {
            category.setCreateTime(createTime.toLocalDateTime());
        }

        Timestamp updateTime = rs.getTimestamp("update_time");
        if (updateTime != null) {
            category.setUpdateTime(updateTime.toLocalDateTime());
        }

        return category;
    }
}